package dao.book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import entity.Book;
import utils.ComonUtil;
import utils.SqlConnection;

/**
 * @Author: Lin Hongwei
 * @LastEditors: Lin Hongwei
 * @email: 1365755396@qq.com
 * @CSDN: https://blog.csdn.net/weixin_45429409
 * @Description: 
 *
 */
public class BookDaoImpl implements BookDao {

	@Override
	public List<Book> getBookList(Connection con, Book book) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet res = null;
		List<Book> bookList = new ArrayList<Book>();
		if (con != null) {
			StringBuffer stringBuffer = new StringBuffer();
			stringBuffer.append("select * from book ");
			ArrayList<Object> paramList = new ArrayList<Object>();
	
			if (!ComonUtil.isEmpty(book.getBookName())) {
				paramList.add("%" + book.getBookName() + "%");
				stringBuffer.append("and bookName like ? ");
			}
			if (!ComonUtil.isEmpty(book.getBookAuthor())) {
				paramList.add("%" + book.getBookAuthor() + "%");
				stringBuffer.append("and bookAuthor like ? ");// 模糊查询
			}
			if (!ComonUtil.isEmpty(book.getBookPrice())) {
				paramList.add( book.getBookPrice() );
				stringBuffer.append("and bookPrice = ? ");// 模糊查询
			}
			if (!ComonUtil.isEmpty(book.getBookPress())) {
				paramList.add("%" + book.getBookPress() + "%");
				stringBuffer.append("and bookPress like ? ");// 模糊查询
			}
			if (!ComonUtil.isEmpty(book.getBookPressTime())) {
				paramList.add("%" + book.getBookPressTime() + "%");
				stringBuffer.append("and bookPressTime like ? ");// 模糊查询
			}
			if (!ComonUtil.isEmpty(book.getBookDesc())) {
				paramList.add("%" + book.getBookDesc() + "%");
				stringBuffer.append("and bookDesc like ? ");// 模糊查询
			}
			
			if(!ComonUtil.isEmpty(book.getBookType())) {
				paramList.add(book.getBookType());
				stringBuffer.append("and bookType = ? ");
			}
			if(book.getBorrowingNumbers()!=-1) {
				paramList.add(book.getBorrowingNumbers());
				stringBuffer.append("and borrowingNumbers = ? ");
			}
			if(book.getId()!=-1) {
				paramList.add(book.getId());
				stringBuffer.append("and id = ? ");
			}
			
			String sql = stringBuffer.toString().replaceFirst("and", "where");
			System.out.println(sql);
			Object[] params = paramList.toArray();
			res = SqlConnection.executeQuery(con, pstm, res, sql, params);
			
			while(res.next()) {
				Book book1 = new Book();
				book1.setId(res.getInt("id"));
				book1.setBookAuthor(res.getString("bookAuthor"));
				book1.setBookDesc(res.getString("bookDesc"));
				book1.setBookName(res.getString("bookName"));
				book1.setBookPress(res.getString("bookPress"));
				book1.setBookPressTime(res.getString("bookPressTime"));
				book1.setBookPrice(res.getString("bookPrice"));
				book1.setBorrowingNumbers(res.getInt("borrowingNumbers"));
				book1.setBookType(res.getString("bookType"));
				bookList.add(book1);
			}
			SqlConnection.closeConnection(con, pstm, res);
		}
		return bookList;
	}

	@Override
	public int addBook(Connection con, Book book) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		int row = 0;

		if(con!=null) {
			String sql = "insert into book (bookName,bookAuthor,bookPrice,bookType,borrowingNumbers,bookPress,bookPressTime,bookDesc) values(?,?,?,?,?,?,?,?)";
			Object[] params = {
					book.getBookName(),
					book.getBookAuthor(),
					book.getBookPrice(),
					book.getBookType(),
					book.getBorrowingNumbers(),
					book.getBookPress(),
					book.getBookPressTime(),
					book.getBookDesc()
			};
			
			con = SqlConnection.getConnection();
			row = SqlConnection.executeUpdate(con, pstm, sql, params);
			SqlConnection.closeConnection(con, pstm, null);
		}
		
		return row;
	}

	@Override
	public int delBook(Connection con, int id) throws Exception {
		// TODO Auto-generated method stub

		PreparedStatement pstm = null;
		int row = 0 ;
		if(con!=null) {
			String sql = "delete from book where id = ?";
			Object[] params = {id};
			row = SqlConnection.executeUpdate(con, pstm, sql, params);
		}
		return row;
	}

	@Override
	public int upBook(Connection con, Book book) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm =null;
		int row = 0;
		
		if(con!=null) {
			StringBuffer stringBuffer = new StringBuffer();
			stringBuffer.append("update book set ");
			ArrayList<Object> paramsList = new ArrayList<Object>();
			
			
			if (!ComonUtil.isEmpty(book.getBookName())) {
				paramsList.add(book.getBookName());
				stringBuffer.append("bookName = ?,");
			}
			if (!ComonUtil.isEmpty(book.getBookAuthor())) {
				paramsList.add( book.getBookAuthor());
				stringBuffer.append("bookAuthor = ?,");
			}
			if (!ComonUtil.isEmpty(book.getBookPrice())) {
				paramsList.add( book.getBookPrice() );
				stringBuffer.append("bookPrice = ?,");
			}
			if (!ComonUtil.isEmpty(book.getBookPress())) {
				paramsList.add( book.getBookPress() );
				stringBuffer.append("bookPress = ?,");
			}
			if (!ComonUtil.isEmpty(book.getBookPressTime())) {
				paramsList.add( book.getBookPressTime() );
				stringBuffer.append("bookPressTime = ?,");
			}
			if (!ComonUtil.isEmpty(book.getBookDesc())) {
				paramsList.add( book.getBookDesc());
				stringBuffer.append("bookDesc = ?,");
			}
			
			if(!ComonUtil.isEmpty(book.getBookType())) {
				paramsList.add(book.getBookType());
				stringBuffer.append("bookType = ?,");
			}
			if(book.getBorrowingNumbers()!=-1) {
				paramsList.add(book.getBorrowingNumbers());
				stringBuffer.append("borrowingNumbers = ?,");
			}
			

			
			String sql = stringBuffer.toString();
//			System.out.println(sql.substring(sql.length()-1));
			if(sql.substring(sql.length()-1).equals(",")) {
				sql = sql.substring(0,sql.length()-1);
			}
			sql+=" where id = ?";
			paramsList.add(book.getId());
			System.out.println("修改语句："+sql);
			
			Object[] params = paramsList.toArray();
			row = SqlConnection.executeUpdate(con, pstm, sql, params);
			SqlConnection.closeConnection(con, pstm, null);
		}
		
		
		return row;
	}

}
